﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.SIESystemManager.Calculate
{
    public class GetSqlData
    {
        public DataTable GetSql(string DRAWING_NUMBER, string Part_Number, string startTime, string endTime)
        {
            string sql = "", sqlDRAWING = "", sqlPARTNO = "";
            sql = @"SELECT A.PartNumber
    ,A.CREATE_DATE
	,A.Drawing_Num
	,A.Width_1
	,A.Depth_1
	,A.Width_2
	,A.Depth_2
	,A.Thickness
	,A.D_Cutting_Size
	,A.Description
	,case when A.routing!='' then LEFT(A.routing, LEN(A.routing) - 1) else '' end AS routing
	,B.[GROUP_CODE]
	,B.[GROUP_DEC]
	,'' AS 'Calculate'
	,30 AS 'WeeksH'
	,[COMPONENT_TYPE]
	,[EDGE_THICKNESS]
      ,[D11_Drilling_vertical_blind_hole]
      ,[D11_Drilling_vertical_through_hole]
      ,[D11_Drilling_Vertical_Sleeve_Hole]
      ,[D11_Drilling_horizontal_front_hole]
      ,[D11_Drilling_Horizontal_Rear_Hole]
      ,[D11_Horizontal_drilling]
      ,[D11_Drilling_Horizontal_Downhole]
      ,[D11_Round_Gong_45_blind_hole]
      ,[D11_Round_Gong_45_through_hole]
      ,[D11_Round_Gong_70_blind_hole]
      ,[D11_Round_Gong_70_through_hole]
      ,[D11_Round_Gong_71_blind_hole]
      ,[D11_Round_Gong_71_through_hole]
      ,[D11_Number_of_special_gongs]
      ,[D11_Length_of_Special_shaped_Gong]
      ,[D11_Width_of_Special_shaped_Gong]
      ,[D11_Gong_lock_block_hole]
      ,[D11_Gong_handle_hole]
      ,[D11_Number_of_sawblade_grooves]
      ,[D11_Saw_blade_groove_length]
      ,[D11_Width_of_saw_blade_groove]
      ,[D21_Component_Type]
      ,[D21_Coefficient_Breadth_W1]
      ,[D21_Coefficient_Breadth_W2]
      ,[D21_Outer_Notch_of_Gong_400]
      ,[D21_Outer_Notch_of_Gong_401]
      ,[D21_Drilling_Vertical_Faces]
      ,[D21_Drilling_Horizontal_Face]
      ,[D21_Round_Gong_80_Holes]
      ,[D21_Round_Gong_81_Holes]
      ,[D21_Gong_Inverted_Plate_500]
      ,[D21_Gong_Inverted_Plate_501]
      ,[D21_Gong_Flange_Reaming]
      ,[D21_Number_of_Special_Gongs]
      ,[D21_Length_of_Special_shaped_Gong]
      ,[D21_Width_of_Special_shaped_Gong]
      ,[D21_Gong_Lock_Block_Hole]
      ,[D21_Gong_Handle_Through_Hole]
      ,[D21_Number_of_Sawblade_Grooves]
      ,[D21_Saw_blade_Groove_Length]
      ,[D21_Saw_blade_Groove_Width]
	  ,[D24_Coefficient_Width_W1]
      ,[D24_Coefficient_Width_W2]
      ,[D24_coefficient_depth_D1]
      ,[D24_coefficient_depth_D2]
      ,[D24_Coefficient_Rotation_Angle_Z]
      ,[D24_Plate_Surface_Gong_Notch]
      ,[D24_Plate_Surface_Gong_Roundness]
      ,[D24_Plate_Surface_Cutting_and_Sealing_Angle]
      ,[D24_Seal_Linear_Edge]
      ,[D24_Gong_Plane_Blind_Groove]
      ,[D24_Gong_Plane_Groove]
      ,[D24_Drilling_Vertical_Blind_Hole]
      ,[D24_Drilling_Vertical_Through_Hole]
      ,[D24_Drilling_The_Closure_Hole]
      ,[D24_Drilling_Flat_Reamer]
      ,[D24_Groove_Side_Rubbing]
      ,[D24_Copper_Head_Plug]
      ,[D24_Twisted_Tooth_Nut]
      ,[D24_LOGO]
      ,[D24_General_Label]
      ,[D24_Packing_Brush_Strip]
      ,[D27_Spray_Glue_And_Tincture]
      ,[D27_Lock_Block_Screw]
      ,[D27_Loaded_L_Type_Tape]
      ,[D27_Fittings_For_Door_Tongue]
      ,[D27_Door_Tape]
      ,[D27_Copper_Head_Rubber_Plug]
      ,[D27_Twisted_Tooth_Nut]
      ,[D27_Groove_Side_Rubbing]
      ,[D27_General_Label]
      ,[D27_Paste_White_Adhesive_Paper]
      ,[D27_LOGO]
      ,[D27_Lock_Self_Tapping_Screw]
      ,[D27_Double_Sided_Adhesive_Paper]
      ,[D27_Mounting_Screen_Hanging_Code]
      ,[D27_Decorative_Hole_Cover]
      ,[D28_Sealing_Diagonal_Edge]
      ,[D29_Seal_Straight_Line_Edge]
      ,[D30_Drilling_vertical_blind_hole]
      ,[D30_Drilling_vertical_through_hole]
      ,[D30_Drilling_Vertical_Sleeve_Hole]
      ,[D30_Drilling_horizontal_front_hole]
      ,[D30_Drilling_Horizontal_Rear_Hole]
      ,[D30_Horizontal_drilling]
      ,[D30_Drilling_Horizontal_Downhole]
      ,[D30_Round_Gong_45_blind_hole]
      ,[D30_Round_Gong_45_through_hole]
      ,[D30_Round_Gong_70_blind_hole]
      ,[D30_Round_Gong_70_through_hole]
      ,[D30_Round_Gong_71_blind_hole]
      ,[D30_Round_Gong_71_through_hole]
      ,[D30_Number_of_special_gongs]
      ,[D30_Length_of_Special_shaped_Gong]
      ,[D30_Width_of_Special_shaped_Gong]
      ,[D30_Gong_lock_block_hole]
      ,[D30_Gong_handle_hole]
      ,[D30_Number_of_sawblade_grooves]
      ,[D30_Saw_blade_groove_length]
      ,[D30_Width_of_saw_blade_groove]
      ,[D31_Seal_Straight_Line_Edge]
      ,[D32_Component_Type]
      ,[D32_Coefficient_Breadth_W1]
      ,[D32_Coefficient_Breadth_W2]
      ,[D32_Outer_Notch_of_Gong_400]
      ,[D32_Outer_Notch_of_Gong_401]
      ,[D32_Drilling_Vertical_Faces]
      ,[D32_Drilling_Horizontal_Face]
      ,[D32_Round_Gong_80_Holes]
      ,[D32_Round_Gong_81_Holes]
      ,[D32_Gong_Inverted_Plate_500]
      ,[D32_Gong_Inverted_Plate_501]
      ,[D32_Gong_Flange_Reaming]
      ,[D32_Number_of_Special_Gongs]
      ,[D32_Length_of_Special_shaped_Gong]
      ,[D32_Width_of_Special_shaped_Gong]
      ,[D32_Gong_Lock_Block_Hole]
      ,[D32_Gong_Handle_Through_Hole]
      ,[D32_Number_of_Sawblade_Grooves]
      ,[D32_Saw_blade_Groove_Length]
      ,[D32_Saw_blade_Groove_Width]
      ,[D34_Component_Type]
      ,[D34_Edge_Thickness]
      ,[D34_Coefficient_Width_W1]
      ,[D34_Coefficient_Width_W2]
      ,[D34_Coefficient_Depth_D1]
      ,[D34_Coefficient_Depth_D2]
      ,[D34_Coefficient_Angle_Z]
      ,[D34_Inside_the_Seal_700]
      ,[D34_Inside_the_Seal_701]
      ,[D34_Seal_Outer_Ear_200]
      ,[D34_Seal_Outer_Ear_400]
      ,[D34_Seal_Outer_Ear_401]
      ,[D34_Outside_Seal_300]
      ,[D34_Outside_Seal_500]
      ,[D34_Outside_Seal_501]
      ,[D34_Groove_Side_Rubbing]
      ,[D34_Copper_Head_Plug]
      ,[D34_Twisted_Tooth_Nut]
      ,[D34_LOGO]
      ,[D34_Mounting_Screen_Hanging_Code]
      ,[D34_Decorative_Hole_Cover]
      ,[D37_Wash_Brush_Board]
      ,[D37_Drilling_Hinge]
      ,[D37_Mounted_Countertop_Flat_Hinge]
      ,[D37_Copper_Head_Rubber_Plug]
      ,[D37_Twisted_Tooth_Nut]
      ,[D37_Decorative_Hole_Cover]
      ,[D37_LOGO]
      ,[D41_Coefficient_Width_W1]
      ,[D41_Coefficient_Width_W2]
      ,[D41_Coefficient_Depth_D1]
      ,[D41_Coefficient_Depth_D2]
      ,[D41_Coefficient_Angle_Z]
      ,[D41_Installation_Of_Door_Tape]
      ,[D41_L_Type_Tape]
      ,[D41_Plate_Thickness_Cut_Slope]
      ,[D41_Shape_Of_Plate_Gong]
      ,[D41_Slab_Bevel_Angle]
      ,[D41_Gong_Plane_Blind_Groove]
      ,[D41_Gong_Lock_Slot_Side]
      ,[D41_Gong_Lock_Plate_Groove_Flat]
      ,[D41_Drilling_Vertical_Blind_Hole]
      ,[D41_Drilling_Vertical_Through_Hole]
      ,[D41_Drilling_Horizontal_Side_Hole]
      ,[D41_Copper_Head_Plug]
      ,[D41_Twisted_Tooth_Nut]
      ,[D41_General_Label]
      ,[D41_Slot_Side_Scrubbing]
      ,[D41_Lock_Tapping_Screw]
      ,[D41_Paste_White_Adhesive_Paper]
      ,[D41_LOGO]
      ,[D41_Double_Sided_Adhesive_Paper]
      ,[D41_Mounting_Screen_Hanging_Code]
      ,[D41_Decorative_Hole_Cover]
,[D24_Plate_Thickness_Cut_Slope]
,[D41_Fittings_For_Door_Tongue]
,[D34_General_Label]
,[D34_Paste_White_Adhesive_Paper]
,[D34_Lock_Self_Tapping_Screw]
,[D34_Double_Sided_Adhesive_Paper]
FROM (
	SELECT SDATA.PartNumber
        ,CREATE_DATE
		,SDATA.Drawing_Num
		,SDATA.Width_1
		,SDATA.Depth_1
		,SDATA.Width_2
		,SDATA.Depth_2
		,SDATA.Thickness
		,SDATA.D_Cutting_Size
		,SDATA.Description
		,CASE 
			WHEN (LEN(SDATA.Procedure_01)) > 2
				THEN (substring(SDATA.Procedure_01, 0, charindex('_', SDATA.Procedure_01))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_02)) > 2
				THEN (substring(SDATA.Procedure_02, 0, charindex('_', SDATA.Procedure_02))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_03)) > 2
				THEN (substring(SDATA.Procedure_03, 0, charindex('_', SDATA.Procedure_03))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_04)) > 2
				THEN (substring(SDATA.Procedure_04, 0, charindex('_', SDATA.Procedure_04))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_05)) > 2
				THEN (substring(SDATA.Procedure_05, 0, charindex('_', SDATA.Procedure_05))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_06)) > 2
				THEN (substring(SDATA.Procedure_06, 0, charindex('_', SDATA.Procedure_06))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_07)) > 2
				THEN (substring(SDATA.Procedure_07, 0, charindex('_', SDATA.Procedure_07))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_08)) > 2
				THEN (substring(SDATA.Procedure_08, 0, charindex('_', SDATA.Procedure_08))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_09)) > 2
				THEN (substring(SDATA.Procedure_09, 0, charindex('_', SDATA.Procedure_09))) + '-'
			ELSE ''
			END + CASE 
			WHEN (LEN(SDATA.Procedure_10)) > 2
				THEN (substring(SDATA.Procedure_10, 0, charindex('_', SDATA.Procedure_10))) + '-'
			ELSE ''
			END AS routing
		,[COMPONENT_TYPE]
		,[EDGE_THICKNESS]
      ,[D11_Drilling_vertical_blind_hole]
      ,[D11_Drilling_vertical_through_hole]
      ,[D11_Drilling_Vertical_Sleeve_Hole]
      ,[D11_Drilling_horizontal_front_hole]
      ,[D11_Drilling_Horizontal_Rear_Hole]
      ,[D11_Horizontal_drilling]
      ,[D11_Drilling_Horizontal_Downhole]
      ,[D11_Round_Gong_45_blind_hole]
      ,[D11_Round_Gong_45_through_hole]
      ,[D11_Round_Gong_70_blind_hole]
      ,[D11_Round_Gong_70_through_hole]
      ,[D11_Round_Gong_71_blind_hole]
      ,[D11_Round_Gong_71_through_hole]
      ,[D11_Number_of_special_gongs]
      ,[D11_Length_of_Special_shaped_Gong]
      ,[D11_Width_of_Special_shaped_Gong]
      ,[D11_Gong_lock_block_hole]
      ,[D11_Gong_handle_hole]
      ,[D11_Number_of_sawblade_grooves]
      ,[D11_Saw_blade_groove_length]
      ,[D11_Width_of_saw_blade_groove]
      ,[D21_Component_Type]
      ,[D21_Coefficient_Breadth_W1]
      ,[D21_Coefficient_Breadth_W2]
      ,[D21_Outer_Notch_of_Gong_400]
      ,[D21_Outer_Notch_of_Gong_401]
      ,[D21_Drilling_Vertical_Faces]
      ,[D21_Drilling_Horizontal_Face]
      ,[D21_Round_Gong_80_Holes]
      ,[D21_Round_Gong_81_Holes]
      ,[D21_Gong_Inverted_Plate_500]
      ,[D21_Gong_Inverted_Plate_501]
      ,[D21_Gong_Flange_Reaming]
      ,[D21_Number_of_Special_Gongs]
      ,[D21_Length_of_Special_shaped_Gong]
      ,[D21_Width_of_Special_shaped_Gong]
      ,[D21_Gong_Lock_Block_Hole]
      ,[D21_Gong_Handle_Through_Hole]
      ,[D21_Number_of_Sawblade_Grooves]
      ,[D21_Saw_blade_Groove_Length]
      ,[D21_Saw_blade_Groove_Width]
	  ,[D24_Coefficient_Width_W1]
      ,[D24_Coefficient_Width_W2]
      ,[D24_coefficient_depth_D1]
      ,[D24_coefficient_depth_D2]
      ,[D24_Coefficient_Rotation_Angle_Z]
      ,[D24_Plate_Surface_Gong_Notch]
      ,[D24_Plate_Surface_Gong_Roundness]
      ,[D24_Plate_Surface_Cutting_and_Sealing_Angle]
      ,[D24_Seal_Linear_Edge]
      ,[D24_Gong_Plane_Blind_Groove]
      ,[D24_Gong_Plane_Groove]
      ,[D24_Drilling_Vertical_Blind_Hole]
      ,[D24_Drilling_Vertical_Through_Hole]
      ,[D24_Drilling_The_Closure_Hole]
      ,[D24_Drilling_Flat_Reamer]
      ,[D24_Groove_Side_Rubbing]
      ,[D24_Copper_Head_Plug]
      ,[D24_Twisted_Tooth_Nut]
      ,[D24_LOGO]
      ,[D24_General_Label]
      ,[D24_Packing_Brush_Strip]
      ,[D27_Spray_Glue_And_Tincture]
      ,[D27_Lock_Block_Screw]
      ,[D27_Loaded_L_Type_Tape]
      ,[D27_Fittings_For_Door_Tongue]
      ,[D27_Door_Tape]
      ,[D27_Copper_Head_Rubber_Plug]
      ,[D27_Twisted_Tooth_Nut]
      ,[D27_Groove_Side_Rubbing]
      ,[D27_General_Label]
      ,[D27_Paste_White_Adhesive_Paper]
      ,[D27_LOGO]
      ,[D27_Lock_Self_Tapping_Screw]
      ,[D27_Double_Sided_Adhesive_Paper]
      ,[D27_Mounting_Screen_Hanging_Code]
      ,[D27_Decorative_Hole_Cover]
      ,[D28_Sealing_Diagonal_Edge]
      ,[D29_Seal_Straight_Line_Edge]
      ,[D30_Drilling_vertical_blind_hole]
      ,[D30_Drilling_vertical_through_hole]
      ,[D30_Drilling_Vertical_Sleeve_Hole]
      ,[D30_Drilling_horizontal_front_hole]
      ,[D30_Drilling_Horizontal_Rear_Hole]
      ,[D30_Horizontal_drilling]
      ,[D30_Drilling_Horizontal_Downhole]
      ,[D30_Round_Gong_45_blind_hole]
      ,[D30_Round_Gong_45_through_hole]
      ,[D30_Round_Gong_70_blind_hole]
      ,[D30_Round_Gong_70_through_hole]
      ,[D30_Round_Gong_71_blind_hole]
      ,[D30_Round_Gong_71_through_hole]
      ,[D30_Number_of_special_gongs]
      ,[D30_Length_of_Special_shaped_Gong]
      ,[D30_Width_of_Special_shaped_Gong]
      ,[D30_Gong_lock_block_hole]
      ,[D30_Gong_handle_hole]
      ,[D30_Number_of_sawblade_grooves]
      ,[D30_Saw_blade_groove_length]
      ,[D30_Width_of_saw_blade_groove]
      ,[D31_Seal_Straight_Line_Edge]
      ,[D32_Component_Type]
      ,[D32_Coefficient_Breadth_W1]
      ,[D32_Coefficient_Breadth_W2]
      ,[D32_Outer_Notch_of_Gong_400]
      ,[D32_Outer_Notch_of_Gong_401]
      ,[D32_Drilling_Vertical_Faces]
      ,[D32_Drilling_Horizontal_Face]
      ,[D32_Round_Gong_80_Holes]
      ,[D32_Round_Gong_81_Holes]
      ,[D32_Gong_Inverted_Plate_500]
      ,[D32_Gong_Inverted_Plate_501]
      ,[D32_Gong_Flange_Reaming]
      ,[D32_Number_of_Special_Gongs]
      ,[D32_Length_of_Special_shaped_Gong]
      ,[D32_Width_of_Special_shaped_Gong]
      ,[D32_Gong_Lock_Block_Hole]
      ,[D32_Gong_Handle_Through_Hole]
      ,[D32_Number_of_Sawblade_Grooves]
      ,[D32_Saw_blade_Groove_Length]
      ,[D32_Saw_blade_Groove_Width]
      ,[D34_Component_Type]
      ,[D34_Edge_Thickness]
      ,[D34_Coefficient_Width_W1]
      ,[D34_Coefficient_Width_W2]
      ,[D34_Coefficient_Depth_D1]
      ,[D34_Coefficient_Depth_D2]
      ,[D34_Coefficient_Angle_Z]
      ,[D34_Inside_the_Seal_700]
      ,[D34_Inside_the_Seal_701]
      ,[D34_Seal_Outer_Ear_200]
      ,[D34_Seal_Outer_Ear_400]
      ,[D34_Seal_Outer_Ear_401]
      ,[D34_Outside_Seal_300]
      ,[D34_Outside_Seal_500]
      ,[D34_Outside_Seal_501]
      ,[D34_Groove_Side_Rubbing]
      ,[D34_Copper_Head_Plug]
      ,[D34_Twisted_Tooth_Nut]
      ,[D34_LOGO]
      ,[D34_Mounting_Screen_Hanging_Code]
      ,[D34_Decorative_Hole_Cover]
      ,[D37_Wash_Brush_Board]
      ,[D37_Drilling_Hinge]
      ,[D37_Mounted_Countertop_Flat_Hinge]
      ,[D37_Copper_Head_Rubber_Plug]
      ,[D37_Twisted_Tooth_Nut]
      ,[D37_Decorative_Hole_Cover]
      ,[D37_LOGO]
      ,[D41_Coefficient_Width_W1]
      ,[D41_Coefficient_Width_W2]
      ,[D41_Coefficient_Depth_D1]
      ,[D41_Coefficient_Depth_D2]
      ,[D41_Coefficient_Angle_Z]
      ,[D41_Installation_Of_Door_Tape]
      ,[D41_L_Type_Tape]
      ,[D41_Plate_Thickness_Cut_Slope]
      ,[D41_Shape_Of_Plate_Gong]
      ,[D41_Slab_Bevel_Angle]
      ,[D41_Gong_Plane_Blind_Groove]
      ,[D41_Gong_Lock_Slot_Side]
      ,[D41_Gong_Lock_Plate_Groove_Flat]
      ,[D41_Drilling_Vertical_Blind_Hole]
      ,[D41_Drilling_Vertical_Through_Hole]
      ,[D41_Drilling_Horizontal_Side_Hole]
      ,[D41_Copper_Head_Plug]
      ,[D41_Twisted_Tooth_Nut]
      ,[D41_General_Label]
      ,[D41_Slot_Side_Scrubbing]
      ,[D41_Lock_Tapping_Screw]
      ,[D41_Paste_White_Adhesive_Paper]
      ,[D41_LOGO]
      ,[D41_Double_Sided_Adhesive_Paper]
      ,[D41_Mounting_Screen_Hanging_Code]
      ,[D41_Decorative_Hole_Cover]
,[D24_Plate_Thickness_Cut_Slope]
,[D41_Fittings_For_Door_Tongue]
,[D34_General_Label]
,[D34_Paste_White_Adhesive_Paper]
,[D34_Lock_Self_Tapping_Screw]
,[D34_Double_Sided_Adhesive_Paper]
	FROM [LamexMESManager].[dbo].[B_IE_Operation_Data] IE
	JOIN [Edgeband_Program].[dbo].[Initial_Data] SDATA ON IE.DRAWING_NUMBER = SDATA.Drawing_Num COLLATE Chinese_PRC_CI_AI_WS) A 
LEFT JOIN [dbo].[B_IE_ROUTING_GROUP_JM] B ON A.routing = B.[GROUP_ROUTING] + '-' COLLATE Chinese_PRC_CI_AI_WS
WHERE 1=1 and A.CREATE_DATE>='" + startTime + "' and  A.CREATE_DATE<='" + endTime + "'";

            sqlDRAWING = string.IsNullOrEmpty(DRAWING_NUMBER) ? " and A.Drawing_Num like '%%'" : DRAWING_NUMBER;
            sqlPARTNO = string.IsNullOrEmpty(Part_Number) ? " and A.PartNumber like '%%'" : Part_Number;
            sql = sql + sqlDRAWING + sqlPARTNO;
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetSJSql(string DRAWING_NUMBER, string Part_Number, string startTime, string endTime)
        {
            string sql = "", sqlDRAWING = "", sqlPARTNO = "";
            sql = @"SELECT DISTINCT
A.PartNumber
    ,A.CREATE_DATE
	,A.Drawing_Num
	,A.Width_1
	,A.Depth_1
	,A.Width_2
	,A.Depth_2
	,A.Thickness
	,A.D_Cutting_Size
	,A.Description
	,case when A.routing!='' then LEFT(A.routing, LEN(A.routing) - 1) else '' end AS routing
	,B.[GROUP_CODE]
	,B.[GROUP_DEC]
	,'' AS 'Calculate'
	,30 AS 'WeeksH'
	,[COMPONENT_TYPE]
	,[EDGE_THICKNESS]
      ,[D11_Drilling_vertical_blind_hole]
      ,[D11_Drilling_vertical_through_hole]
      ,[D11_Drilling_Vertical_Sleeve_Hole]
      ,[D11_Drilling_horizontal_front_hole]
      ,[D11_Drilling_Horizontal_Rear_Hole]
      ,[D11_Horizontal_drilling]
      ,[D11_Drilling_Horizontal_Downhole]
      ,[D11_Round_Gong_45_blind_hole]
      ,[D11_Round_Gong_45_through_hole]
      ,[D11_Round_Gong_70_blind_hole]
      ,[D11_Round_Gong_70_through_hole]
      ,[D11_Round_Gong_71_blind_hole]
      ,[D11_Round_Gong_71_through_hole]
      ,[D11_Number_of_special_gongs]
      ,[D11_Length_of_Special_shaped_Gong]
      ,[D11_Width_of_Special_shaped_Gong]
      ,[D11_Gong_lock_block_hole]
      ,[D11_Gong_handle_hole]
      ,[D11_Number_of_sawblade_grooves]
      ,[D11_Saw_blade_groove_length]
      ,[D11_Width_of_saw_blade_groove]
      ,[D21_Component_Type]
      ,[D21_Coefficient_Breadth_W1]
      ,[D21_Coefficient_Breadth_W2]
      ,[D21_Outer_Notch_of_Gong_400]
      ,[D21_Outer_Notch_of_Gong_401]
      ,[D21_Drilling_Vertical_Faces]
      ,[D21_Drilling_Horizontal_Face]
      ,[D21_Round_Gong_80_Holes]
      ,[D21_Round_Gong_81_Holes]
      ,[D21_Gong_Inverted_Plate_500]
      ,[D21_Gong_Inverted_Plate_501]
      ,[D21_Gong_Flange_Reaming]
      ,[D21_Number_of_Special_Gongs]
      ,[D21_Length_of_Special_shaped_Gong]
      ,[D21_Width_of_Special_shaped_Gong]
      ,[D21_Gong_Lock_Block_Hole]
      ,[D21_Gong_Handle_Through_Hole]
      ,[D21_Number_of_Sawblade_Grooves]
      ,[D21_Saw_blade_Groove_Length]
      ,[D21_Saw_blade_Groove_Width]
	  ,[D24_Coefficient_Width_W1]
      ,[D24_Coefficient_Width_W2]
      ,[D24_coefficient_depth_D1]
      ,[D24_coefficient_depth_D2]
      ,[D24_Coefficient_Rotation_Angle_Z]
      ,[D24_Plate_Surface_Gong_Notch]
      ,[D24_Plate_Surface_Gong_Roundness]
      ,[D24_Plate_Surface_Cutting_and_Sealing_Angle]
      ,[D24_Seal_Linear_Edge]
      ,[D24_Gong_Plane_Blind_Groove]
      ,[D24_Gong_Plane_Groove]
      ,[D24_Drilling_Vertical_Blind_Hole]
      ,[D24_Drilling_Vertical_Through_Hole]
      ,[D24_Drilling_The_Closure_Hole]
      ,[D24_Drilling_Flat_Reamer]
      ,[D24_Groove_Side_Rubbing]
      ,[D24_Copper_Head_Plug]
      ,[D24_Twisted_Tooth_Nut]
      ,[D24_LOGO]
      ,[D24_General_Label]
      ,[D24_Packing_Brush_Strip]
      ,[D27_Spray_Glue_And_Tincture]
      ,[D27_Lock_Block_Screw]
      ,[D27_Loaded_L_Type_Tape]
      ,[D27_Fittings_For_Door_Tongue]
      ,[D27_Door_Tape]
      ,[D27_Copper_Head_Rubber_Plug]
      ,[D27_Twisted_Tooth_Nut]
      ,[D27_Groove_Side_Rubbing]
      ,[D27_General_Label]
      ,[D27_Paste_White_Adhesive_Paper]
      ,[D27_LOGO]
      ,[D27_Lock_Self_Tapping_Screw]
      ,[D27_Double_Sided_Adhesive_Paper]
      ,[D27_Mounting_Screen_Hanging_Code]
      ,[D27_Decorative_Hole_Cover]
      ,[D28_Sealing_Diagonal_Edge]
      ,[D29_Seal_Straight_Line_Edge]
      ,[D30_Drilling_vertical_blind_hole]
      ,[D30_Drilling_vertical_through_hole]
      ,[D30_Drilling_Vertical_Sleeve_Hole]
      ,[D30_Drilling_horizontal_front_hole]
      ,[D30_Drilling_Horizontal_Rear_Hole]
      ,[D30_Horizontal_drilling]
      ,[D30_Drilling_Horizontal_Downhole]
      ,[D30_Round_Gong_45_blind_hole]
      ,[D30_Round_Gong_45_through_hole]
      ,[D30_Round_Gong_70_blind_hole]
      ,[D30_Round_Gong_70_through_hole]
      ,[D30_Round_Gong_71_blind_hole]
      ,[D30_Round_Gong_71_through_hole]
      ,[D30_Number_of_special_gongs]
      ,[D30_Length_of_Special_shaped_Gong]
      ,[D30_Width_of_Special_shaped_Gong]
      ,[D30_Gong_lock_block_hole]
      ,[D30_Gong_handle_hole]
      ,[D30_Number_of_sawblade_grooves]
      ,[D30_Saw_blade_groove_length]
      ,[D30_Width_of_saw_blade_groove]
      ,[D31_Seal_Straight_Line_Edge]
      ,[D32_Component_Type]
      ,[D32_Coefficient_Breadth_W1]
      ,[D32_Coefficient_Breadth_W2]
      ,[D32_Outer_Notch_of_Gong_400]
      ,[D32_Outer_Notch_of_Gong_401]
      ,[D32_Drilling_Vertical_Faces]
      ,[D32_Drilling_Horizontal_Face]
      ,[D32_Round_Gong_80_Holes]
      ,[D32_Round_Gong_81_Holes]
      ,[D32_Gong_Inverted_Plate_500]
      ,[D32_Gong_Inverted_Plate_501]
      ,[D32_Gong_Flange_Reaming]
      ,[D32_Number_of_Special_Gongs]
      ,[D32_Length_of_Special_shaped_Gong]
      ,[D32_Width_of_Special_shaped_Gong]
      ,[D32_Gong_Lock_Block_Hole]
      ,[D32_Gong_Handle_Through_Hole]
      ,[D32_Number_of_Sawblade_Grooves]
      ,[D32_Saw_blade_Groove_Length]
      ,[D32_Saw_blade_Groove_Width]
      ,[D34_Component_Type]
      ,[D34_Edge_Thickness]
      ,[D34_Coefficient_Width_W1]
      ,[D34_Coefficient_Width_W2]
      ,[D34_Coefficient_Depth_D1]
      ,[D34_Coefficient_Depth_D2]
      ,[D34_Coefficient_Angle_Z]
      ,[D34_Inside_the_Seal_700]
      ,[D34_Inside_the_Seal_701]
      ,[D34_Seal_Outer_Ear_200]
      ,[D34_Seal_Outer_Ear_400]
      ,[D34_Seal_Outer_Ear_401]
      ,[D34_Outside_Seal_300]
      ,[D34_Outside_Seal_500]
      ,[D34_Outside_Seal_501]
      ,[D34_Groove_Side_Rubbing]
      ,[D34_Copper_Head_Plug]
      ,[D34_Twisted_Tooth_Nut]
      ,[D34_LOGO]
      ,[D34_Mounting_Screen_Hanging_Code]
      ,[D34_Decorative_Hole_Cover]
      ,[D37_Wash_Brush_Board]
      ,[D37_Drilling_Hinge]
      ,[D37_Mounted_Countertop_Flat_Hinge]
      ,[D37_Copper_Head_Rubber_Plug]
      ,[D37_Twisted_Tooth_Nut]
      ,[D37_Decorative_Hole_Cover]
      ,[D37_LOGO]
      ,[D41_Coefficient_Width_W1]
      ,[D41_Coefficient_Width_W2]
      ,[D41_Coefficient_Depth_D1]
      ,[D41_Coefficient_Depth_D2]
      ,[D41_Coefficient_Angle_Z]
      ,[D41_Installation_Of_Door_Tape]
      ,[D41_L_Type_Tape]
      ,[D41_Plate_Thickness_Cut_Slope]
      ,[D41_Shape_Of_Plate_Gong]
      ,[D41_Slab_Bevel_Angle]
      ,[D41_Gong_Plane_Blind_Groove]
      ,[D41_Gong_Lock_Slot_Side]
      ,[D41_Gong_Lock_Plate_Groove_Flat]
      ,[D41_Drilling_Vertical_Blind_Hole]
      ,[D41_Drilling_Vertical_Through_Hole]
      ,[D41_Drilling_Horizontal_Side_Hole]
      ,[D41_Copper_Head_Plug]
      ,[D41_Twisted_Tooth_Nut]
      ,[D41_General_Label]
      ,[D41_Slot_Side_Scrubbing]
      ,[D41_Lock_Tapping_Screw]
      ,[D41_Paste_White_Adhesive_Paper]
      ,[D41_LOGO]
      ,[D41_Double_Sided_Adhesive_Paper]
      ,[D41_Mounting_Screen_Hanging_Code]
      ,[D41_Decorative_Hole_Cover]
,[D24_Plate_Thickness_Cut_Slope]
,[D41_Fittings_For_Door_Tongue]
,[D34_General_Label]
,[D34_Paste_White_Adhesive_Paper]
,[D34_Lock_Self_Tapping_Screw]
,[D34_Double_Sided_Adhesive_Paper]
FROM (
	SELECT SDATA.PartNumber
        ,CREATE_DATE
		,SDATA.Drawing_Num
		,SDATA.Width_1
		,SDATA.Depth_1
		,SDATA.Width_2
		,SDATA.Depth_2
		,SDATA.Thickness
		,SDATA.D_Cutting_Size
		,SDATA.Description
		,(SELECT WORKCENTER_NUMBER+'-' from [Edgeband_Program].[dbo].ROUTING_TEMP_DATA 
where PART_NUMBER=RT.PART_NUMBER  ORDER BY PROCESS_STEP for XML PATH('') )  AS routing
		,[COMPONENT_TYPE]
		,[EDGE_THICKNESS]
      ,[D11_Drilling_vertical_blind_hole]
      ,[D11_Drilling_vertical_through_hole]
      ,[D11_Drilling_Vertical_Sleeve_Hole]
      ,[D11_Drilling_horizontal_front_hole]
      ,[D11_Drilling_Horizontal_Rear_Hole]
      ,[D11_Horizontal_drilling]
      ,[D11_Drilling_Horizontal_Downhole]
      ,[D11_Round_Gong_45_blind_hole]
      ,[D11_Round_Gong_45_through_hole]
      ,[D11_Round_Gong_70_blind_hole]
      ,[D11_Round_Gong_70_through_hole]
      ,[D11_Round_Gong_71_blind_hole]
      ,[D11_Round_Gong_71_through_hole]
      ,[D11_Number_of_special_gongs]
      ,[D11_Length_of_Special_shaped_Gong]
      ,[D11_Width_of_Special_shaped_Gong]
      ,[D11_Gong_lock_block_hole]
      ,[D11_Gong_handle_hole]
      ,[D11_Number_of_sawblade_grooves]
      ,[D11_Saw_blade_groove_length]
      ,[D11_Width_of_saw_blade_groove]
      ,[D21_Component_Type]
      ,[D21_Coefficient_Breadth_W1]
      ,[D21_Coefficient_Breadth_W2]
      ,[D21_Outer_Notch_of_Gong_400]
      ,[D21_Outer_Notch_of_Gong_401]
      ,[D21_Drilling_Vertical_Faces]
      ,[D21_Drilling_Horizontal_Face]
      ,[D21_Round_Gong_80_Holes]
      ,[D21_Round_Gong_81_Holes]
      ,[D21_Gong_Inverted_Plate_500]
      ,[D21_Gong_Inverted_Plate_501]
      ,[D21_Gong_Flange_Reaming]
      ,[D21_Number_of_Special_Gongs]
      ,[D21_Length_of_Special_shaped_Gong]
      ,[D21_Width_of_Special_shaped_Gong]
      ,[D21_Gong_Lock_Block_Hole]
      ,[D21_Gong_Handle_Through_Hole]
      ,[D21_Number_of_Sawblade_Grooves]
      ,[D21_Saw_blade_Groove_Length]
      ,[D21_Saw_blade_Groove_Width]
	  ,[D24_Coefficient_Width_W1]
      ,[D24_Coefficient_Width_W2]
      ,[D24_coefficient_depth_D1]
      ,[D24_coefficient_depth_D2]
      ,[D24_Coefficient_Rotation_Angle_Z]
      ,[D24_Plate_Surface_Gong_Notch]
      ,[D24_Plate_Surface_Gong_Roundness]
      ,[D24_Plate_Surface_Cutting_and_Sealing_Angle]
      ,[D24_Seal_Linear_Edge]
      ,[D24_Gong_Plane_Blind_Groove]
      ,[D24_Gong_Plane_Groove]
      ,[D24_Drilling_Vertical_Blind_Hole]
      ,[D24_Drilling_Vertical_Through_Hole]
      ,[D24_Drilling_The_Closure_Hole]
      ,[D24_Drilling_Flat_Reamer]
      ,[D24_Groove_Side_Rubbing]
      ,[D24_Copper_Head_Plug]
      ,[D24_Twisted_Tooth_Nut]
      ,[D24_LOGO]
      ,[D24_General_Label]
      ,[D24_Packing_Brush_Strip]
      ,[D27_Spray_Glue_And_Tincture]
      ,[D27_Lock_Block_Screw]
      ,[D27_Loaded_L_Type_Tape]
      ,[D27_Fittings_For_Door_Tongue]
      ,[D27_Door_Tape]
      ,[D27_Copper_Head_Rubber_Plug]
      ,[D27_Twisted_Tooth_Nut]
      ,[D27_Groove_Side_Rubbing]
      ,[D27_General_Label]
      ,[D27_Paste_White_Adhesive_Paper]
      ,[D27_LOGO]
      ,[D27_Lock_Self_Tapping_Screw]
      ,[D27_Double_Sided_Adhesive_Paper]
      ,[D27_Mounting_Screen_Hanging_Code]
      ,[D27_Decorative_Hole_Cover]
      ,[D28_Sealing_Diagonal_Edge]
      ,[D29_Seal_Straight_Line_Edge]
      ,[D30_Drilling_vertical_blind_hole]
      ,[D30_Drilling_vertical_through_hole]
      ,[D30_Drilling_Vertical_Sleeve_Hole]
      ,[D30_Drilling_horizontal_front_hole]
      ,[D30_Drilling_Horizontal_Rear_Hole]
      ,[D30_Horizontal_drilling]
      ,[D30_Drilling_Horizontal_Downhole]
      ,[D30_Round_Gong_45_blind_hole]
      ,[D30_Round_Gong_45_through_hole]
      ,[D30_Round_Gong_70_blind_hole]
      ,[D30_Round_Gong_70_through_hole]
      ,[D30_Round_Gong_71_blind_hole]
      ,[D30_Round_Gong_71_through_hole]
      ,[D30_Number_of_special_gongs]
      ,[D30_Length_of_Special_shaped_Gong]
      ,[D30_Width_of_Special_shaped_Gong]
      ,[D30_Gong_lock_block_hole]
      ,[D30_Gong_handle_hole]
      ,[D30_Number_of_sawblade_grooves]
      ,[D30_Saw_blade_groove_length]
      ,[D30_Width_of_saw_blade_groove]
      ,[D31_Seal_Straight_Line_Edge]
      ,[D32_Component_Type]
      ,[D32_Coefficient_Breadth_W1]
      ,[D32_Coefficient_Breadth_W2]
      ,[D32_Outer_Notch_of_Gong_400]
      ,[D32_Outer_Notch_of_Gong_401]
      ,[D32_Drilling_Vertical_Faces]
      ,[D32_Drilling_Horizontal_Face]
      ,[D32_Round_Gong_80_Holes]
      ,[D32_Round_Gong_81_Holes]
      ,[D32_Gong_Inverted_Plate_500]
      ,[D32_Gong_Inverted_Plate_501]
      ,[D32_Gong_Flange_Reaming]
      ,[D32_Number_of_Special_Gongs]
      ,[D32_Length_of_Special_shaped_Gong]
      ,[D32_Width_of_Special_shaped_Gong]
      ,[D32_Gong_Lock_Block_Hole]
      ,[D32_Gong_Handle_Through_Hole]
      ,[D32_Number_of_Sawblade_Grooves]
      ,[D32_Saw_blade_Groove_Length]
      ,[D32_Saw_blade_Groove_Width]
      ,[D34_Component_Type]
      ,[D34_Edge_Thickness]
      ,[D34_Coefficient_Width_W1]
      ,[D34_Coefficient_Width_W2]
      ,[D34_Coefficient_Depth_D1]
      ,[D34_Coefficient_Depth_D2]
      ,[D34_Coefficient_Angle_Z]
      ,[D34_Inside_the_Seal_700]
      ,[D34_Inside_the_Seal_701]
      ,[D34_Seal_Outer_Ear_200]
      ,[D34_Seal_Outer_Ear_400]
      ,[D34_Seal_Outer_Ear_401]
      ,[D34_Outside_Seal_300]
      ,[D34_Outside_Seal_500]
      ,[D34_Outside_Seal_501]
      ,[D34_Groove_Side_Rubbing]
      ,[D34_Copper_Head_Plug]
      ,[D34_Twisted_Tooth_Nut]
      ,[D34_LOGO]
      ,[D34_Mounting_Screen_Hanging_Code]
      ,[D34_Decorative_Hole_Cover]
      ,[D37_Wash_Brush_Board]
      ,[D37_Drilling_Hinge]
      ,[D37_Mounted_Countertop_Flat_Hinge]
      ,[D37_Copper_Head_Rubber_Plug]
      ,[D37_Twisted_Tooth_Nut]
      ,[D37_Decorative_Hole_Cover]
      ,[D37_LOGO]
      ,[D41_Coefficient_Width_W1]
      ,[D41_Coefficient_Width_W2]
      ,[D41_Coefficient_Depth_D1]
      ,[D41_Coefficient_Depth_D2]
      ,[D41_Coefficient_Angle_Z]
      ,[D41_Installation_Of_Door_Tape]
      ,[D41_L_Type_Tape]
      ,[D41_Plate_Thickness_Cut_Slope]
      ,[D41_Shape_Of_Plate_Gong]
      ,[D41_Slab_Bevel_Angle]
      ,[D41_Gong_Plane_Blind_Groove]
      ,[D41_Gong_Lock_Slot_Side]
      ,[D41_Gong_Lock_Plate_Groove_Flat]
      ,[D41_Drilling_Vertical_Blind_Hole]
      ,[D41_Drilling_Vertical_Through_Hole]
      ,[D41_Drilling_Horizontal_Side_Hole]
      ,[D41_Copper_Head_Plug]
      ,[D41_Twisted_Tooth_Nut]
      ,[D41_General_Label]
      ,[D41_Slot_Side_Scrubbing]
      ,[D41_Lock_Tapping_Screw]
      ,[D41_Paste_White_Adhesive_Paper]
      ,[D41_LOGO]
      ,[D41_Double_Sided_Adhesive_Paper]
      ,[D41_Mounting_Screen_Hanging_Code]
      ,[D41_Decorative_Hole_Cover]
,[D24_Plate_Thickness_Cut_Slope]
,[D41_Fittings_For_Door_Tongue]
,[D34_General_Label]
,[D34_Paste_White_Adhesive_Paper]
,[D34_Lock_Self_Tapping_Screw]
,[D34_Double_Sided_Adhesive_Paper]
	FROM [LamexMESManager].[dbo].[B_IE_Operation_Data] IE
JOIN [Edgeband_Program].[dbo].[Initial_Data_SJ] SDATA ON IE.DRAWING_NUMBER = SDATA.PartNumber COLLATE Chinese_PRC_CI_AI_WS
JOIN [Edgeband_Program].[dbo].[ROUTING_TEMP_DATA] RT ON RT.PART_NUMBER=SDATA.PartNumber 
 COLLATE Chinese_PRC_CI_AI_WS
) A 
LEFT JOIN [dbo].[B_IE_ROUTING_GROUP_JM] B ON A.routing = B.[GROUP_ROUTING] + '-' COLLATE Chinese_PRC_CI_AI_WS
WHERE 1=1 and A.CREATE_DATE>='" + startTime + "' and  A.CREATE_DATE<='" + endTime + "'";

            sqlDRAWING = string.IsNullOrEmpty(DRAWING_NUMBER) ? " and A.Drawing_Num like '%%'" : DRAWING_NUMBER;
            sqlPARTNO = string.IsNullOrEmpty(Part_Number) ? " and A.PartNumber like '%%'" : Part_Number;
            sql = sql + sqlPARTNO;
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetSJSql(string Part_Number)
        {
            string sql = "";
            sql = @"SELECT DISTINCT
A.PartNumber
    ,A.CREATE_DATE
	,A.Drawing_Num
	,A.Width_1
	,A.Depth_1
	,A.Width_2
	,A.Depth_2
	,A.Thickness
	,A.D_Cutting_Size
	,A.Description
	,case when A.routing!='' then LEFT(A.routing, LEN(A.routing) - 1) else '' end AS routing
	,B.[GROUP_CODE]
	,B.[GROUP_DEC]
	,'' AS 'Calculate'
	,30 AS 'WeeksH'
	,[COMPONENT_TYPE]
	,[EDGE_THICKNESS]
      ,[D11_Drilling_vertical_blind_hole]
      ,[D11_Drilling_vertical_through_hole]
      ,[D11_Drilling_Vertical_Sleeve_Hole]
      ,[D11_Drilling_horizontal_front_hole]
      ,[D11_Drilling_Horizontal_Rear_Hole]
      ,[D11_Horizontal_drilling]
      ,[D11_Drilling_Horizontal_Downhole]
      ,[D11_Round_Gong_45_blind_hole]
      ,[D11_Round_Gong_45_through_hole]
      ,[D11_Round_Gong_70_blind_hole]
      ,[D11_Round_Gong_70_through_hole]
      ,[D11_Round_Gong_71_blind_hole]
      ,[D11_Round_Gong_71_through_hole]
      ,[D11_Number_of_special_gongs]
      ,[D11_Length_of_Special_shaped_Gong]
      ,[D11_Width_of_Special_shaped_Gong]
      ,[D11_Gong_lock_block_hole]
      ,[D11_Gong_handle_hole]
      ,[D11_Number_of_sawblade_grooves]
      ,[D11_Saw_blade_groove_length]
      ,[D11_Width_of_saw_blade_groove]
      ,[D21_Component_Type]
      ,[D21_Coefficient_Breadth_W1]
      ,[D21_Coefficient_Breadth_W2]
      ,[D21_Outer_Notch_of_Gong_400]
      ,[D21_Outer_Notch_of_Gong_401]
      ,[D21_Drilling_Vertical_Faces]
      ,[D21_Drilling_Horizontal_Face]
      ,[D21_Round_Gong_80_Holes]
      ,[D21_Round_Gong_81_Holes]
      ,[D21_Gong_Inverted_Plate_500]
      ,[D21_Gong_Inverted_Plate_501]
      ,[D21_Gong_Flange_Reaming]
      ,[D21_Number_of_Special_Gongs]
      ,[D21_Length_of_Special_shaped_Gong]
      ,[D21_Width_of_Special_shaped_Gong]
      ,[D21_Gong_Lock_Block_Hole]
      ,[D21_Gong_Handle_Through_Hole]
      ,[D21_Number_of_Sawblade_Grooves]
      ,[D21_Saw_blade_Groove_Length]
      ,[D21_Saw_blade_Groove_Width]
	  ,[D24_Coefficient_Width_W1]
      ,[D24_Coefficient_Width_W2]
      ,[D24_coefficient_depth_D1]
      ,[D24_coefficient_depth_D2]
      ,[D24_Coefficient_Rotation_Angle_Z]
      ,[D24_Plate_Surface_Gong_Notch]
      ,[D24_Plate_Surface_Gong_Roundness]
      ,[D24_Plate_Surface_Cutting_and_Sealing_Angle]
      ,[D24_Seal_Linear_Edge]
      ,[D24_Gong_Plane_Blind_Groove]
      ,[D24_Gong_Plane_Groove]
      ,[D24_Drilling_Vertical_Blind_Hole]
      ,[D24_Drilling_Vertical_Through_Hole]
      ,[D24_Drilling_The_Closure_Hole]
      ,[D24_Drilling_Flat_Reamer]
      ,[D24_Groove_Side_Rubbing]
      ,[D24_Copper_Head_Plug]
      ,[D24_Twisted_Tooth_Nut]
      ,[D24_LOGO]
      ,[D24_General_Label]
      ,[D24_Packing_Brush_Strip]
      ,[D27_Spray_Glue_And_Tincture]
      ,[D27_Lock_Block_Screw]
      ,[D27_Loaded_L_Type_Tape]
      ,[D27_Fittings_For_Door_Tongue]
      ,[D27_Door_Tape]
      ,[D27_Copper_Head_Rubber_Plug]
      ,[D27_Twisted_Tooth_Nut]
      ,[D27_Groove_Side_Rubbing]
      ,[D27_General_Label]
      ,[D27_Paste_White_Adhesive_Paper]
      ,[D27_LOGO]
      ,[D27_Lock_Self_Tapping_Screw]
      ,[D27_Double_Sided_Adhesive_Paper]
      ,[D27_Mounting_Screen_Hanging_Code]
      ,[D27_Decorative_Hole_Cover]
      ,[D28_Sealing_Diagonal_Edge]
      ,[D29_Seal_Straight_Line_Edge]
      ,[D30_Drilling_vertical_blind_hole]
      ,[D30_Drilling_vertical_through_hole]
      ,[D30_Drilling_Vertical_Sleeve_Hole]
      ,[D30_Drilling_horizontal_front_hole]
      ,[D30_Drilling_Horizontal_Rear_Hole]
      ,[D30_Horizontal_drilling]
      ,[D30_Drilling_Horizontal_Downhole]
      ,[D30_Round_Gong_45_blind_hole]
      ,[D30_Round_Gong_45_through_hole]
      ,[D30_Round_Gong_70_blind_hole]
      ,[D30_Round_Gong_70_through_hole]
      ,[D30_Round_Gong_71_blind_hole]
      ,[D30_Round_Gong_71_through_hole]
      ,[D30_Number_of_special_gongs]
      ,[D30_Length_of_Special_shaped_Gong]
      ,[D30_Width_of_Special_shaped_Gong]
      ,[D30_Gong_lock_block_hole]
      ,[D30_Gong_handle_hole]
      ,[D30_Number_of_sawblade_grooves]
      ,[D30_Saw_blade_groove_length]
      ,[D30_Width_of_saw_blade_groove]
      ,[D31_Seal_Straight_Line_Edge]
      ,[D32_Component_Type]
      ,[D32_Coefficient_Breadth_W1]
      ,[D32_Coefficient_Breadth_W2]
      ,[D32_Outer_Notch_of_Gong_400]
      ,[D32_Outer_Notch_of_Gong_401]
      ,[D32_Drilling_Vertical_Faces]
      ,[D32_Drilling_Horizontal_Face]
      ,[D32_Round_Gong_80_Holes]
      ,[D32_Round_Gong_81_Holes]
      ,[D32_Gong_Inverted_Plate_500]
      ,[D32_Gong_Inverted_Plate_501]
      ,[D32_Gong_Flange_Reaming]
      ,[D32_Number_of_Special_Gongs]
      ,[D32_Length_of_Special_shaped_Gong]
      ,[D32_Width_of_Special_shaped_Gong]
      ,[D32_Gong_Lock_Block_Hole]
      ,[D32_Gong_Handle_Through_Hole]
      ,[D32_Number_of_Sawblade_Grooves]
      ,[D32_Saw_blade_Groove_Length]
      ,[D32_Saw_blade_Groove_Width]
      ,[D34_Component_Type]
      ,[D34_Edge_Thickness]
      ,[D34_Coefficient_Width_W1]
      ,[D34_Coefficient_Width_W2]
      ,[D34_Coefficient_Depth_D1]
      ,[D34_Coefficient_Depth_D2]
      ,[D34_Coefficient_Angle_Z]
      ,[D34_Inside_the_Seal_700]
      ,[D34_Inside_the_Seal_701]
      ,[D34_Seal_Outer_Ear_200]
      ,[D34_Seal_Outer_Ear_400]
      ,[D34_Seal_Outer_Ear_401]
      ,[D34_Outside_Seal_300]
      ,[D34_Outside_Seal_500]
      ,[D34_Outside_Seal_501]
      ,[D34_Groove_Side_Rubbing]
      ,[D34_Copper_Head_Plug]
      ,[D34_Twisted_Tooth_Nut]
      ,[D34_LOGO]
      ,[D34_Mounting_Screen_Hanging_Code]
      ,[D34_Decorative_Hole_Cover]
      ,[D37_Wash_Brush_Board]
      ,[D37_Drilling_Hinge]
      ,[D37_Mounted_Countertop_Flat_Hinge]
      ,[D37_Copper_Head_Rubber_Plug]
      ,[D37_Twisted_Tooth_Nut]
      ,[D37_Decorative_Hole_Cover]
      ,[D37_LOGO]
      ,[D41_Coefficient_Width_W1]
      ,[D41_Coefficient_Width_W2]
      ,[D41_Coefficient_Depth_D1]
      ,[D41_Coefficient_Depth_D2]
      ,[D41_Coefficient_Angle_Z]
      ,[D41_Installation_Of_Door_Tape]
      ,[D41_L_Type_Tape]
      ,[D41_Plate_Thickness_Cut_Slope]
      ,[D41_Shape_Of_Plate_Gong]
      ,[D41_Slab_Bevel_Angle]
      ,[D41_Gong_Plane_Blind_Groove]
      ,[D41_Gong_Lock_Slot_Side]
      ,[D41_Gong_Lock_Plate_Groove_Flat]
      ,[D41_Drilling_Vertical_Blind_Hole]
      ,[D41_Drilling_Vertical_Through_Hole]
      ,[D41_Drilling_Horizontal_Side_Hole]
      ,[D41_Copper_Head_Plug]
      ,[D41_Twisted_Tooth_Nut]
      ,[D41_General_Label]
      ,[D41_Slot_Side_Scrubbing]
      ,[D41_Lock_Tapping_Screw]
      ,[D41_Paste_White_Adhesive_Paper]
      ,[D41_LOGO]
      ,[D41_Double_Sided_Adhesive_Paper]
      ,[D41_Mounting_Screen_Hanging_Code]
      ,[D41_Decorative_Hole_Cover]
,[D24_Plate_Thickness_Cut_Slope]
,[D41_Fittings_For_Door_Tongue]
,[D34_General_Label]
,[D34_Paste_White_Adhesive_Paper]
,[D34_Lock_Self_Tapping_Screw]
,[D34_Double_Sided_Adhesive_Paper]
FROM (
	SELECT SDATA.PartNumber
        ,CREATE_DATE
		,SDATA.Drawing_Num
		,SDATA.Width_1
		,SDATA.Depth_1
		,SDATA.Width_2
		,SDATA.Depth_2
		,SDATA.Thickness
		,SDATA.D_Cutting_Size
		,SDATA.Description
		,(SELECT WORKCENTER_NUMBER+'-' from [Edgeband_Program].[dbo].ROUTING_TEMP_DATA 
where PART_NUMBER=RT.PART_NUMBER  ORDER BY PROCESS_STEP for XML PATH('') )  AS routing
		,[COMPONENT_TYPE]
		,[EDGE_THICKNESS]
      ,[D11_Drilling_vertical_blind_hole]
      ,[D11_Drilling_vertical_through_hole]
      ,[D11_Drilling_Vertical_Sleeve_Hole]
      ,[D11_Drilling_horizontal_front_hole]
      ,[D11_Drilling_Horizontal_Rear_Hole]
      ,[D11_Horizontal_drilling]
      ,[D11_Drilling_Horizontal_Downhole]
      ,[D11_Round_Gong_45_blind_hole]
      ,[D11_Round_Gong_45_through_hole]
      ,[D11_Round_Gong_70_blind_hole]
      ,[D11_Round_Gong_70_through_hole]
      ,[D11_Round_Gong_71_blind_hole]
      ,[D11_Round_Gong_71_through_hole]
      ,[D11_Number_of_special_gongs]
      ,[D11_Length_of_Special_shaped_Gong]
      ,[D11_Width_of_Special_shaped_Gong]
      ,[D11_Gong_lock_block_hole]
      ,[D11_Gong_handle_hole]
      ,[D11_Number_of_sawblade_grooves]
      ,[D11_Saw_blade_groove_length]
      ,[D11_Width_of_saw_blade_groove]
      ,[D21_Component_Type]
      ,[D21_Coefficient_Breadth_W1]
      ,[D21_Coefficient_Breadth_W2]
      ,[D21_Outer_Notch_of_Gong_400]
      ,[D21_Outer_Notch_of_Gong_401]
      ,[D21_Drilling_Vertical_Faces]
      ,[D21_Drilling_Horizontal_Face]
      ,[D21_Round_Gong_80_Holes]
      ,[D21_Round_Gong_81_Holes]
      ,[D21_Gong_Inverted_Plate_500]
      ,[D21_Gong_Inverted_Plate_501]
      ,[D21_Gong_Flange_Reaming]
      ,[D21_Number_of_Special_Gongs]
      ,[D21_Length_of_Special_shaped_Gong]
      ,[D21_Width_of_Special_shaped_Gong]
      ,[D21_Gong_Lock_Block_Hole]
      ,[D21_Gong_Handle_Through_Hole]
      ,[D21_Number_of_Sawblade_Grooves]
      ,[D21_Saw_blade_Groove_Length]
      ,[D21_Saw_blade_Groove_Width]
	  ,[D24_Coefficient_Width_W1]
      ,[D24_Coefficient_Width_W2]
      ,[D24_coefficient_depth_D1]
      ,[D24_coefficient_depth_D2]
      ,[D24_Coefficient_Rotation_Angle_Z]
      ,[D24_Plate_Surface_Gong_Notch]
      ,[D24_Plate_Surface_Gong_Roundness]
      ,[D24_Plate_Surface_Cutting_and_Sealing_Angle]
      ,[D24_Seal_Linear_Edge]
      ,[D24_Gong_Plane_Blind_Groove]
      ,[D24_Gong_Plane_Groove]
      ,[D24_Drilling_Vertical_Blind_Hole]
      ,[D24_Drilling_Vertical_Through_Hole]
      ,[D24_Drilling_The_Closure_Hole]
      ,[D24_Drilling_Flat_Reamer]
      ,[D24_Groove_Side_Rubbing]
      ,[D24_Copper_Head_Plug]
      ,[D24_Twisted_Tooth_Nut]
      ,[D24_LOGO]
      ,[D24_General_Label]
      ,[D24_Packing_Brush_Strip]
      ,[D27_Spray_Glue_And_Tincture]
      ,[D27_Lock_Block_Screw]
      ,[D27_Loaded_L_Type_Tape]
      ,[D27_Fittings_For_Door_Tongue]
      ,[D27_Door_Tape]
      ,[D27_Copper_Head_Rubber_Plug]
      ,[D27_Twisted_Tooth_Nut]
      ,[D27_Groove_Side_Rubbing]
      ,[D27_General_Label]
      ,[D27_Paste_White_Adhesive_Paper]
      ,[D27_LOGO]
      ,[D27_Lock_Self_Tapping_Screw]
      ,[D27_Double_Sided_Adhesive_Paper]
      ,[D27_Mounting_Screen_Hanging_Code]
      ,[D27_Decorative_Hole_Cover]
      ,[D28_Sealing_Diagonal_Edge]
      ,[D29_Seal_Straight_Line_Edge]
      ,[D30_Drilling_vertical_blind_hole]
      ,[D30_Drilling_vertical_through_hole]
      ,[D30_Drilling_Vertical_Sleeve_Hole]
      ,[D30_Drilling_horizontal_front_hole]
      ,[D30_Drilling_Horizontal_Rear_Hole]
      ,[D30_Horizontal_drilling]
      ,[D30_Drilling_Horizontal_Downhole]
      ,[D30_Round_Gong_45_blind_hole]
      ,[D30_Round_Gong_45_through_hole]
      ,[D30_Round_Gong_70_blind_hole]
      ,[D30_Round_Gong_70_through_hole]
      ,[D30_Round_Gong_71_blind_hole]
      ,[D30_Round_Gong_71_through_hole]
      ,[D30_Number_of_special_gongs]
      ,[D30_Length_of_Special_shaped_Gong]
      ,[D30_Width_of_Special_shaped_Gong]
      ,[D30_Gong_lock_block_hole]
      ,[D30_Gong_handle_hole]
      ,[D30_Number_of_sawblade_grooves]
      ,[D30_Saw_blade_groove_length]
      ,[D30_Width_of_saw_blade_groove]
      ,[D31_Seal_Straight_Line_Edge]
      ,[D32_Component_Type]
      ,[D32_Coefficient_Breadth_W1]
      ,[D32_Coefficient_Breadth_W2]
      ,[D32_Outer_Notch_of_Gong_400]
      ,[D32_Outer_Notch_of_Gong_401]
      ,[D32_Drilling_Vertical_Faces]
      ,[D32_Drilling_Horizontal_Face]
      ,[D32_Round_Gong_80_Holes]
      ,[D32_Round_Gong_81_Holes]
      ,[D32_Gong_Inverted_Plate_500]
      ,[D32_Gong_Inverted_Plate_501]
      ,[D32_Gong_Flange_Reaming]
      ,[D32_Number_of_Special_Gongs]
      ,[D32_Length_of_Special_shaped_Gong]
      ,[D32_Width_of_Special_shaped_Gong]
      ,[D32_Gong_Lock_Block_Hole]
      ,[D32_Gong_Handle_Through_Hole]
      ,[D32_Number_of_Sawblade_Grooves]
      ,[D32_Saw_blade_Groove_Length]
      ,[D32_Saw_blade_Groove_Width]
      ,[D34_Component_Type]
      ,[D34_Edge_Thickness]
      ,[D34_Coefficient_Width_W1]
      ,[D34_Coefficient_Width_W2]
      ,[D34_Coefficient_Depth_D1]
      ,[D34_Coefficient_Depth_D2]
      ,[D34_Coefficient_Angle_Z]
      ,[D34_Inside_the_Seal_700]
      ,[D34_Inside_the_Seal_701]
      ,[D34_Seal_Outer_Ear_200]
      ,[D34_Seal_Outer_Ear_400]
      ,[D34_Seal_Outer_Ear_401]
      ,[D34_Outside_Seal_300]
      ,[D34_Outside_Seal_500]
      ,[D34_Outside_Seal_501]
      ,[D34_Groove_Side_Rubbing]
      ,[D34_Copper_Head_Plug]
      ,[D34_Twisted_Tooth_Nut]
      ,[D34_LOGO]
      ,[D34_Mounting_Screen_Hanging_Code]
      ,[D34_Decorative_Hole_Cover]
      ,[D37_Wash_Brush_Board]
      ,[D37_Drilling_Hinge]
      ,[D37_Mounted_Countertop_Flat_Hinge]
      ,[D37_Copper_Head_Rubber_Plug]
      ,[D37_Twisted_Tooth_Nut]
      ,[D37_Decorative_Hole_Cover]
      ,[D37_LOGO]
      ,[D41_Coefficient_Width_W1]
      ,[D41_Coefficient_Width_W2]
      ,[D41_Coefficient_Depth_D1]
      ,[D41_Coefficient_Depth_D2]
      ,[D41_Coefficient_Angle_Z]
      ,[D41_Installation_Of_Door_Tape]
      ,[D41_L_Type_Tape]
      ,[D41_Plate_Thickness_Cut_Slope]
      ,[D41_Shape_Of_Plate_Gong]
      ,[D41_Slab_Bevel_Angle]
      ,[D41_Gong_Plane_Blind_Groove]
      ,[D41_Gong_Lock_Slot_Side]
      ,[D41_Gong_Lock_Plate_Groove_Flat]
      ,[D41_Drilling_Vertical_Blind_Hole]
      ,[D41_Drilling_Vertical_Through_Hole]
      ,[D41_Drilling_Horizontal_Side_Hole]
      ,[D41_Copper_Head_Plug]
      ,[D41_Twisted_Tooth_Nut]
      ,[D41_General_Label]
      ,[D41_Slot_Side_Scrubbing]
      ,[D41_Lock_Tapping_Screw]
      ,[D41_Paste_White_Adhesive_Paper]
      ,[D41_LOGO]
      ,[D41_Double_Sided_Adhesive_Paper]
      ,[D41_Mounting_Screen_Hanging_Code]
      ,[D41_Decorative_Hole_Cover]
,[D24_Plate_Thickness_Cut_Slope]
,[D41_Fittings_For_Door_Tongue]
,[D34_General_Label]
,[D34_Paste_White_Adhesive_Paper]
,[D34_Lock_Self_Tapping_Screw]
,[D34_Double_Sided_Adhesive_Paper]
	FROM [LamexMESManager].[dbo].[B_IE_Operation_Data] IE
JOIN [Edgeband_Program].[dbo].[Initial_Data_SJ] SDATA ON IE.DRAWING_NUMBER = SDATA.PartNumber COLLATE Chinese_PRC_CI_AI_WS
JOIN [Edgeband_Program].[dbo].[ROUTING_TEMP_DATA] RT ON RT.PART_NUMBER=SDATA.PartNumber  COLLATE Chinese_PRC_CI_AI_WS
--JOIN [LamexMESManager].[dbo].[B_IE_SJR_DATA] SJ ON SJ.PartNo=IE.DRAWING_NUMBER AND SJ.FLAG='1' AND Calculate_Flag IS NULL
) A 
LEFT JOIN [dbo].[B_IE_ROUTING_GROUP_JM] B ON A.routing = B.[GROUP_ROUTING] + '-' COLLATE Chinese_PRC_CI_AI_WS
where 1=1  ";

            sql = sql + Part_Number;
            DataTable dt = new DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
    }
}
